// JdbcGradeRepository.java JDBC实现
package com.example.studentmanagement2.repository.JdbcRepository;

import com.example.studentmanagement2.dto.CourseAverageDTO;
import com.example.studentmanagement2.dto.GradeDetail;
import com.example.studentmanagement2.model.Grade;
import com.example.studentmanagement2.repository.Repository.GradeRepository;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class JdbcGradeRepository implements GradeRepository {
    private final JdbcTemplate jdbcTemplate;

    public JdbcGradeRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public Grade save(Grade grade) {
        jdbcTemplate.update(
                "INSERT INTO grades (student_id, course_id, score) VALUES (?, ?, ?)",
                grade.getStudentId(),
                grade.getCourseId(),
                grade.getScore()
        );
        return grade;
    }

    @Override
    public List<Grade> findByStudentId(String studentId) {
        return jdbcTemplate.query(
                "SELECT * FROM grades WHERE student_id = ?",
                new Object[]{studentId},
                (rs, rowNum) -> new Grade(
                        rs.getString("student_id"),
                        rs.getString("course_id"),
                        rs.getDouble("score")
                )
        );
    }

    @Override
    public List<Grade> findByCourseId(String courseId) {
        return null;
    }

    @Override
    public boolean existsByStudentAndCourse(String studentId, String courseId) {
        Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM grades WHERE student_id = ? AND course_id = ?",
                Integer.class,
                studentId,
                courseId
        );
        return count != null && count > 0;
    }

    @Override
    public void update(Grade grade) {
        jdbcTemplate.update(
                "UPDATE grades SET score = ? WHERE student_id = ? AND course_id = ?",
                grade.getScore(),
                grade.getStudentId(),
                grade.getCourseId()
        );
    }
    // JdbcGradeRepository.java 新增查询方法
    public List<GradeDetail> findGradeDetails(String studentId) {
        String sql = """
        SELECT s.id AS student_id, s.name AS student_name, s.class_name, 
               c.course_id, c.course_name, g.score
        FROM grades g
        JOIN students s ON g.student_id = s.id
        JOIN courses c ON g.course_id = c.course_id
        WHERE g.student_id = ?
        """;
        return jdbcTemplate.query(sql, new Object[]{studentId}, (rs, rowNum) ->
                new GradeDetail(
                        rs.getString("student_id"),
                        rs.getString("student_name"),
                        rs.getString("class_name"),
                        rs.getString("course_id"),
                        rs.getString("course_name"),
                        rs.getDouble("score")
                ));
    }

    @Override
    public List<GradeDetail> findByCourseWithDetails(String courseId) {
        return null;
    }

    @Override
    public List<CourseAverageDTO> findClassAverages(String className) {
        String sql = """
            SELECT 
                c.course_name AS courseName, 
                AVG(g.score) AS averageScore 
            FROM grades g 
            JOIN students s ON g.student_id = s.id 
            JOIN courses c ON g.course_id = c.course_id 
            WHERE s.class_name = ? 
            GROUP BY c.course_name
            """;

        return jdbcTemplate.query(
                sql,
                new Object[]{className},
                (rs, rowNum) -> new CourseAverageDTO(
                        rs.getString("courseName"),
                        rs.getDouble("averageScore")
                )
        );
    }
}